

drop table f_login;
drop table f_order;
drop table f_admin;
drop table customer;
drop table f_room;
drop table f_fw;
drop table f_good;


--管理员表
create table f_admin(
       a_id number primary key,  -- 管理员编号 
       a_type varchar2(10)
              constraint CK_atype check(a_type in('1', '2')), -- 1 为会员 2 为管理员
       a_name varchar2(20) unique, -- 管理员姓名
       a_pwd varchar2(50) default 'a', -- 管理员密码
       a_image blob --头像
); 
drop sequence f_admin_id;
create sequence f_admin_id increment by 1 start with 1000;
insert into f_admin values(f_admin_id.nextval, '2', 'navy', '123321', null);
select * from f_admin;


-- 顾客表
create table customer(
       c_id number primary key,  -- 顾客编号 
       c_type varchar2(10)
             constraint CK_ctype check(c_type in('1', '2')), -- 1 为会员 2 为管理员
       c_name varchar2(20) not null, -- 顾客姓名
       c_pwd varchar2(50) default 'a', -- 用户密码
       c_cardid varchar2(20) unique not null, -- 身份证号码
       c_tel varchar2(15) unique not null, -- 手机号码 
       c_sex char(3) default '男' check (c_sex in ('男', '女')), -- 性别
       c_money number, -- 账户余额
       c_eml varchar2(50) unique, -- 邮箱
       c_image blob,
       c_temp varchar2(100) -- 已定房间
);

update customer set c_money = c_money - -14.90 where c_name = '李卓';
drop sequence customer_id;
create sequence customer_id increment by 1 start with 1000;


update customer set c_money = c_money - 10 where c_cardid = '431121233213215411';

Delete from customer where c_name = '李卓'; 
insert into customer values(customer_id.nextval, '1' ,'李三', '123321', '542365200001233561', '15388888887', default, 252.25, '2445222673@qq.com', null, null);
select * from customer;

update customer set c_temp = null where c_name = '李卓';




--房间表
create table f_room(
       r_id varchar2(50) primary key, -- 门牌号
       r_type varchar2(20) not null,  -- 类型
       r_price int not null,  --费用
       r_status int,  --状态  1 空闲 2 打扫中 3 在住 
       r_image  blob, -- 图片
       r_datail varchar(50)  --描述
);
insert into f_room values('101', '单人间', 100, 1, null, null);
insert into f_room values('102', '单人间', 100, 1, null, null);
insert into f_room values('103', '单人间', 100, 1, null, null);
insert into f_room values('104', '单人间', 100, 1, null, null);
insert into f_room values('105', '单人间', 100, 1, null, null);
insert into f_room values('106', '单人间', 100, 1, null, null);
insert into f_room values('201', '双人间', 180, 1, null, null);
insert into f_room values('202', '双人间', 180, 1, null, null);
insert into f_room values('203', '双人间', 180, 1, null, null);
insert into f_room values('204', '双人间', 180, 1, null, null);
insert into f_room values('301', '豪华单人间', 300, 1, null, null);
insert into f_room values('302', '豪华单人间', 300, 1, null, null);
insert into f_room values('303', '豪华单人间', 300, 1, null, null);
insert into f_room values('304', '豪华单人间', 300, 1, null, null);
insert into f_room values('305', '豪华单人间', 300, 1, null, null);
insert into f_room values('401', '总统套房', 1024, 1, null, null);
insert into f_room values('402', '总统套房', 1024, 1, null, null);
insert into f_room values('403', '总统套房', 1024, 1, null, null);
Delete from f_room where r_id != '102';
select * from f_room;
update f_room set r_status = 1 where r_id = '101';
select r_id, r_type, r_price, r_status, r_datail from f_room where r_id like '10%';



drop table f_order;
-- 预定表
create table f_order(
       o_id number primary key, -- 预定编号
       r_id varchar2(50) constraint FK_r_id references f_room(r_id), -- 预定房间门牌号
       c_cardid varchar2(20) unique constraint FK_c_cardid references customer(c_cardid), -- 身份证号码
       start_date date, --预定时间
       r_price number default 0, -- 押金
       bor_temp01  varchar2(255) -- 预留字段01 
);

select r_type, f_room.r_price as price from f_order, f_room where c_cardid = '123456789012345678' and f_order.r_id = f_room.r_id;

drop sequence f_order_id;
create sequence f_order_id increment by 1 start with 1000;
insert into f_order values(f_order_id.nextval, '101', '431121222122123211', to_date('2021-11-26 19', 'yyyy-mm-dd hh24'), 20.2, null);
Delete from f_order where r_id = '101';
select * from f_order;



drop table f_login;

--入住登记表
create table f_login(
       l_id int primary key, --住宿编号
       r_id varchar2(50) constraint FK_fr_id references f_room(r_id), -- 房间门牌号
       c_cardid varchar2(20) constraint FK_fc_cardid references customer(c_cardid), -- 身份证号码
       start_date date, --入住时间
       end_date date, -- 退房时间 
       l_price number  -- 总金额    
);
drop sequence f_login_id;
create sequence f_login_id increment by 1 start with 1000;
delete from f_login where c_cardid = '123456789012345678';
select * from f_login;
select c_cardid from f_login where start_date = to_date('2021-12-09', 'yyyy-mm-dd') and c_cardid = '123456789012345678' and end_date is null
update f_login set l_price = 30 where l_id=1022

update f_login set end_date = to_date('2021-12-09', 'yyyy-mm-dd'), l_price = -20.0 where l_id = 
(select l_id from f_login where c_cardid = '123456789012345678' and  end_date is null  and start_date = 
(select start_date from f_order where r_id =
 (select c_temp from customer where c_cardid = '123456789012345678')))


select c_cardid from f_login where date = to_date(?, 'yyyy-mm-dd') and c_cardid = ?


--商品表
create table f_good(
       g_id int primary key,
       g_name varchar2(20),
       g_price number(5,2),
       g_image blob
);
create sequence f_good_id increment by 1 start with 1001;
insert into f_good values(f_good_id.nextval, '青岛啤酒（雪花）', '5.00', null);
insert into f_good values(f_good_id.nextval, '农夫山泉', '2.00', null);
insert into f_good values(f_good_id.nextval, '中华（金中支）', '80.00', null);
insert into f_good values(f_good_id.nextval, '白沙烟（和天下）', '100.00', null);
insert into f_good values(f_good_id.nextval, '中华（双中支）', '85.00', null);
insert into f_good values(f_good_id.nextval, '农夫山泉', '2.00', null);
insert into f_good values(f_good_id.nextval, '农夫山泉', '2.00', null);
insert into f_good values(f_good_id.nextval, '农夫山泉', '2.00', null);
select * from F_good for update;
select g_id, g_name, g_price, g_image from f_good where 1 = 1 and g_name like '农%';
select g_id, g_name, g_price, g_image from f_good where 1 = 1  and g_name like '%';
drop table f_good;
drop sequence f_good_id;

-- 统计
create table f_fw(
       f_id int primary key,
       l_id int constraint FK_l_id references f_login(l_id), --住宿编号
       g_id int constraint FK_g_id references f_good(g_id),
       f_num int,
       f_price number(5,2)
);
select f.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l_price), sum(f_price), avg(l_price) + sum(f_price) totalprice from f_fw f, f_login l, f_room r, customer c
       where f.l_id = l.l_id and c.c_cardid = l.c_cardid and r.r_id = l.r_id group by f.l_id, c_name, l.r_id, r_type, start_date, end_date;

select g_name, f_num, f_price from f_fw f, f_good g where f.g_id = g.g_id and l_id = '1001';
create sequence f_fw_id increment by 1 start with 1001;
drop table f_fw;
insert into f_fw values(f_fw_id.nextval, 1025, 1001, 1, '5');
insert into f_fw values(f_fw_id.nextval, 1001, 1001, 2, '10.00');
insert into f_fw values(f_fw_id.nextval, 1001, 1002, 1, '2.00');
insert into f_fw values(f_fw_id.nextval, 1001, 1003, 1, '80.00');
insert into f_fw values(f_fw_id.nextval, 1002, 1002, 1, '2.00');
insert into f_fw values(f_fw_id.nextval, 1002, 1002, 1, '2.00');
select * from f_fw;


-- 留言表
create table f_lmessage(
       l_id int primary key,
       l_con varchar2(90)
);
create sequence f_lmessage_id increment by 1 start with 1001;
insert into f_lmessage values(f_lmessage_id.nextval, '吃的很不错');

-- 评价表
create table f_appraise(
       l_id int constraint FK_a_l_id references f_login(l_id) primary key, --住宿编号
       a_id int,
       a_datil varchar2(1000),
       a_reply varchar2(1000),
       a_image blob
);

insert into f_appraise values((select l_id from f_login where end_date is null and start_date = (select start_date from f_order where r_id = '102')), 3, 
'abs', null, null);


drop table f_appraise;
select * from f_appraise;
insert into f_appraise values('1022', '1', '服务质量还行，就是商品太少了', null, null);
delete from f_appraise where l_id = '1025';

select r_id, c_name, a_id, a_datil, a_reply, a_image from f_appraise a, f_login l, customer c
where a.l_id = l.l_id and c.c_cardid = l.c_cardid;

(select l_id from f_login where end_date is null  and start_date = (select start_date from f_order where r_id = (select c_temp from customer where c_cardid = '431121233213215411')))

select * from f_login where start_date = (select start_date from f_order where r_id = (select c_temp from customer where c_cardid = '431121233213215411'));

select l.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l.l), sum(l.f), avg(l.l) + sum(l.f) totalprice from
(select l.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l_price) l, sum(nvl(f_price, 0)) f from  
       (select l.l_id, l.r_id, l_price, c_name, r_type, start_date, end_date from f_login l, customer c, f_room r where c.c_cardid = l.c_cardid and r.r_id = l.r_id) l
       full join f_fw f on f.l_id = l.l_id group by l.l_id, c_name, l.r_id, r_type, start_date, end_date) l
       group by l.l_id, c_name, l.r_id, r_type, start_date, end_date;

select l.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l.l), sum(l.f), avg(l.l) + sum(l.f) totalprice from
(select l.l_id, c_name, l.r_id, r_type, start_date, end_date, avg(l_price) l, sum(nvl(f_price, 0)) f from
 (select l.l_id, l.r_id, l_price, c_name, r_type, start_date, end_date from f_login l, customer c, f_room r where c.c_cardid = l.c_cardid and r.r_id = l.r_id) l
  full join f_fw f on f.l_id = l.l_id group by l.l_id, c_name, l.r_id, r_type, start_date, end_date) l where 1 = 1
   group by l.l_id, c_name, l.r_id, r_type, start_date, end_date 





commit;
rollback;
